package com.yellowpage.nsp.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class RemoteNspUserDAO{
	
	private final static Properties properties = new Properties();
	private static Log logger = LogFactory.getLog(RemoteNspUserDAO.class);
	
	String nsp_db_driver;
	String nsp_db_url;
	String nsp_db_username;
	String nsp_db_password;
	public RemoteNspUserDAO(){
		try {			
			properties.load(this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"));  

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		nsp_db_driver = properties.getProperty("nsp_client_db_driver");
		nsp_db_url = properties.getProperty("nsp_client_db_url");
		nsp_db_username = properties.getProperty("nsp_client_db_username");
		nsp_db_password = properties.getProperty("nsp_client_db_password");
		
		/*nsp_db_driver = "com.mysql.jdbc.Driver";
		nsp_db_url = "jdbc:mysql://101.227.253.194:3306/yp114?userUnicode=true&amp;characterEncoding=utf8";
		nsp_db_username = "root";
		nsp_db_password = "hy114";*/
		
		
	}
	public List<Map> getNspUserAccount(String updateTime) {
		Connection nspConn = null;
        PreparedStatement nspPs = null;
        List<Map> list=new ArrayList<Map>();
        try {
        	//获取数据库连接
        	nspConn = getNSPConnection();
        	if(nspConn!=null){
	        	//执行数据库操作    
        		if(updateTime.equals("0")){
        			nspPs = nspConn.prepareStatement(
    	        			"select id, mobileNo, password, registTime, accountType, accountUser," +
    	                    " submitter, submitTime, updateUser,updateTime,description " +
    	                    "  from user_account ");
        		}else{
        	     	nspPs = nspConn.prepareStatement(
    	        			"select id, mobileNo, password, registTime, accountType, accountUser," +
    	                    " submitter, submitTime, updateUser,updateTime,description " +
    	                    "  from user_account where updateTime >'"+updateTime+"' ");
        		}
	   
	        	
	           ResultSet re = nspPs.executeQuery();
	           //封装对象
	           while (re.next()) {
	        	   Map<String, String> map = new HashMap<String, String>();
	        	   map.put("id", re.getString(1));
	        	   map.put("mobileNo", re.getString(2));
	        	   map.put("password", re.getString(3));
	        	   map.put("registTime", re.getString(4));
	        	   map.put("accountType", re.getString(5));
	        	   map.put("accountUser", re.getString(6));
	        	   map.put("submitter", re.getString(7));
	        	   map.put("submitTime", re.getString(8));
	        	   map.put("updateUser", re.getString(9));
	        	   map.put("updateTime", re.getString(10));
	        	   map.put("description", re.getString(11));

	        	   list.add(map);	        	   
	           }
	           //关闭数据库连接
	        	re.close();
	        	nspPs.close();
	            nspConn.close();
	            
        	}

        } catch (Exception e) {
           logger.error(e);
        } finally {
            try{
                if (nspPs != null)	nspPs.close();
                
                if (nspConn != null) nspConn.close();
            }catch(Exception e){
              logger.error(e);
            }
           
        }
       return list;
    }
	
	public List<Map> getNspUserPayAccount(String updateTime) {
		Connection nspConn = null;
        PreparedStatement nspPs = null;
        List<Map> list=new ArrayList<Map>();
        try {
        	//获取数据库连接
        	nspConn = getNSPConnection();
        	if(nspConn!=null){
	        	//执行数据库操作     
        		if(updateTime.equals("0")){
        			nspPs = nspConn.prepareStatement(
    	        			"select id, mobileNo, discountSource, dicountDesc, effectTime, effectState," +
    	                    " paySource, pay, createTime,payState,submitter,submitTime,updateUser,updateTime,description " +
    	                    "  from user_pay_account");
        		}else{
        			nspPs = nspConn.prepareStatement(
	        			"select id, mobileNo, discountSource, dicountDesc, effectTime, effectState," +
	                    " paySource, pay, createTime,payState,submitter,submitTime,updateUser,updateTime,description " +
	                    "  from user_pay_account where updateTime >'"+updateTime+"' ");
        		}
	        	
	           ResultSet re = nspPs.executeQuery();
	           //封装对象
	           while (re.next()) {
	        	   Map<String, String> map = new HashMap<String, String>();
	        	   map.put("id", re.getString(1));
	        	   map.put("mobileNo", re.getString(2));
	        	   map.put("discountSource", re.getString(3));
	        	   map.put("dicountDesc", re.getString(4));
	        	   map.put("effectTime", re.getString(5));
	        	   map.put("effectState", re.getString(6));
	        	   map.put("paySource", re.getString(7));
	        	   map.put("pay", re.getString(8));
	        	   
	        	   map.put("createTime", re.getString(9));
	        	   map.put("payState", re.getString(10));
	        	   
	        	   map.put("submitter", re.getString(11));
	        	   map.put("submitTime", re.getString(12));
	        	   map.put("updateUser", re.getString(13));
	        	   map.put("updateTime", re.getString(14));
	        	   map.put("description", re.getString(15));

	        	   list.add(map);	        	   
	           }
	           //关闭数据库连接
	        	re.close();
	        	nspPs.close();
	            nspConn.close();
	            
        	}

        } catch (Exception e) {
           // logger.error(e);
        } finally {
            try{
                if (nspPs != null)	nspPs.close();
                
                if (nspConn != null) nspConn.close();
            }catch(Exception e){
              //  logger.error(e);
            }
           
        }
       return list;
    }
	
	public List<Map> getNspUserPerson(String updateTime) {
		Connection nspConn = null;
        PreparedStatement nspPs = null;
        List<Map> list=new ArrayList<Map>();
        try {
        	//获取数据库连接
        	nspConn = getNSPConnection();
        	if(nspConn!=null){
	        	//执行数据库操作     
        		if(updateTime.equals("0")){
        			nspPs = nspConn.prepareStatement(
    	        			"select id, mobileNo, comAddr, comUser, comtel, zipcode," +
    	                    " merchatId, attentionStock, attentionTime,cancelTime,attentionPrice,attentionPrice,submitter,submitTime,updateUser,updateTime,description " +
    	                    "  from user_person");
        		}else{
        			nspPs = nspConn.prepareStatement(
	        			"select id, mobileNo, comAddr, comUser, comtel, zipcode," +
    	                    " merchatId, attentionStock, attentionTime,cancelTime,attentionPrice,classSequence,submitter,submitTime,updateUser,updateTime,description " +
    	                    "  from user_person where updateTime >'"+updateTime+"' ");
        		}
	        	
	           ResultSet re = nspPs.executeQuery();
	           //封装对象
	           while (re.next()) {
	        	   Map<String, String> map = new HashMap<String, String>();
	        	   map.put("id", re.getString(1));
	        	   map.put("mobileNo", re.getString(2));
	        	   map.put("comAddr", re.getString(3));
	        	   map.put("comUser", re.getString(4));
	        	   map.put("comtel", re.getString(5));
	        	   map.put("zipcode", re.getString(6));
	        	   map.put("merchatId", re.getString(7));
	        	   map.put("attentionStock", re.getString(8));
	        	   
	        	   map.put("attentionTime", re.getString(9));
	        	   map.put("cancelTime", re.getString(10));
	        	   
	        	   map.put("attentionPrice", re.getString(11));
	        	   map.put("classSequence", re.getString(12));
	        	   
	        	   map.put("submitter", re.getString(13));
	        	   map.put("submitTime", re.getString(14));
	        	   map.put("updateUser", re.getString(15));
	        	   map.put("updateTime", re.getString(16));
	        	   map.put("description", re.getString(17));

	        	   list.add(map);	        	   
	           }
	           //关闭数据库连接
	        	re.close();
	        	nspPs.close();
	            nspConn.close();
	            
        	}

        } catch (Exception e) {
           // logger.error(e);
        } finally {
            try{
                if (nspPs != null)	nspPs.close();
                
                if (nspConn != null) nspConn.close();
            }catch(Exception e){
              //  logger.error(e);
            }
           
        }
       return list;
    }

    //获取数控连接
    private Connection getNSPConnection(){
        try {

            Class.forName(nsp_db_driver);
        
            Connection connection = DriverManager.getConnection(nsp_db_url, nsp_db_username, nsp_db_password);

            return connection;

        } catch (Exception e) {
            logger.error(e);
        }
        return null;
    }

}
